*********************************************************************************************************************************************************************************
*** Input files are: CRSP monthly and daily security returns file, crsp-compustat merged file. One needs to have subcrription via to WRDS to download these files. 
*********************************************************************************************************************************************************************************
version 17
clear all 

**********************************************************************************************************************
*** Set directory/path + file names --> directory/path has to be changed depending on the author running the code !!!
**********************************************************************************************************************
																
global path = "C:\Users\H_BOUSTANIFAR\Desktop\Hamid Dropbox Dropbox\Hamid Boustanifar\Nice\PhD supervision\Young Dae Kang\best_brand_paper\"	// set the global path to the folder

global data_path = "$path\Data"	
global output_file_path = "$path\Results"
global compustat_file_name = "ccm" 						                  //annual crsp-compustat merged file 
global crsp_file_name = "crsp"													//monthly CRSP file
global output_data_file_name = "Fama_MacBeth"						//--> final data set only contains Returns and controls for monthly crsp tap shrcd=10/11 and exchcf=1/2/3 (common shares on NYSE, AMEX, NASDAQ)


********************************************************************************************
*** 1. Prepare COMPUSTAT file for merge + create FF industry classifications from COMPUSTAT.
********************************************************************************************

* open COMPUSTAT 
cd "$data_path"
use $compustat_file_name, clear



**********************************************
*** Drop definitley not needed COMPUSTAT vars.
**********************************************
drop acctchg acctstd acqmeth adrr ajex ajp bspr compst curncd currtr curuscn final ismod ltcm ogm pddur scf src stalt udpl upd acchg acco accrt acdo aco acodo acominc acox acoxar acqao acqcshi acqic acqppe acqsc adpac aedi afudcc afudci aldo am amc amdc amgw ano ao aocidergl aociother aocipen aocisecgl aodo aol2 aoloch aox apalch apb apc apofs aqa aqc aqd aqeps aqi aqp aqpl1 aqs arb arc arce arced arceeps artfs aul3 autxr balr banlr bast bastr batr bcef bclr bcltbl bcnlr bcrbl bct bctbl bctr billexce bkvlps bltbl ca capr1 capr2 capr3 caps capsft capx capxv cb cbi cdpac cdvc ceiexbill ceql ceqt cfbd cfere cfo cfpdo cga cgri cgti cgui ch chech chs ci cibegni cicurr cidergl cimii ciother cipen cisecgl citotal cld2 cld3 cld4 cld5 clfc clfx clg clis cll cllc clo clrll clt cmp cnltbl cpcbl cpdoi cpnli cppbl cprei crv crvnli cshfd cshi csho cshpri cshr cshrc cshrp cshrso cshrt cshrw cstk cstkcv cstke dbi dc dclo dcom dcpstk dcs dcvsr dcvsub dcvt dd dd1 dd2 dd3 dd4 dd5 depc derac deralt derhedgl derlc derllt dfpac dfs dfxa diladj dilavx dlcch dltis dlto dltp dltr dltsub dltt dm dn do donr dpacb dpacc dpacli dpacls dpacme dpacnr dpaco dpacre dpact dpc dpdc dpltb dpret dpsc dpstb dptb dptc dptic dpvieb dpvio dpvir drci ds dt dtea dted dteeps dtep dudd dv dvdnp dvintf dvp dvpa dvpd dvpdp dvpibb dvrpiv dvrre dvsco dvt dxd2 dxd3 dxd4 dxd5 ea ebit ebitda eiea emol emp epsfi epsfx epspi epspx esopct esopdlt esopnr esopr esopt esub esubc excadj exre fatb fatc fatd fate fatl fatn fato fatp fca fdfr fea fel ffo ffs fiao finaco finao fincf finch findlc findlt finivst finlco finlto finnp finrecc finreclt finrev finxint finxopr fopo fopox fopt fsrco fsrct fuseo fuset gbbl gdwl gdwlam gdwlia gdwlid gdwlieps gdwlip geqrv gla glcea glced glceeps glcep gld gleps gliv glp govgr govtown gp gphbl gplbl gpobl gprbl gptbl gwo hedgegl iaeq iaeqci iaeqmi iafici iafxi iafxmi iali ialoi ialti iamli iaoi iapli iarei iasci iasmi iassi iasti iatci iati iatmi iaui ibadj ibbl ibc ibcom ibki ibmii icapt idiis idilb idilc idis idist idit idits iire initb intc intpn invch invfg invo invofs invreh invrei invres invrm invwip iobd ioi iore ip ipabl ipc iphbl iplbl ipobl iptbl ipti ipv irei irent irii irli irnli irsi iseq iseqc iseqm isfi isfxc isfxm isgr isgt isgu islg islgc islgm islt isng isngc isngm isotc isoth isotm issc issm issu ist istc istm isut itcb itcc itci ivaco ivaeq ivao ivch ivgod ivi ivncf ivpt ivst ivstch lcabg lcacl lcacr lcag lcal lcalt lcam lcao lcast lcat lco lcox lcoxar lcoxdr lcuacu li lif lifr lifrp lloml lloo llot llrci llrcr llwoci llwocr lno lo lol2 loxdr lqpl1 lrv ls lse lst lul3 mib mibn mibt mii mrc1 mrc2 mrc3 mrc4 mrc5 mrct mrcta msa msvrv mtl nat nco nfsr niadj nieci niint niintpfc niintpfp niit nim nio nipfc nipfp nit nits nopi nopio np npanl npaore nparl npat nrtxt nrtxtd nrtxteps oancf ob oiadp oibdp opeps opili opincar opini opioi opiri opiti oprepsx optca optdr optex optexd optfvgr optgr optlife optosby optosey optprcby optprcca optprcex optprcey optprcgr optprcwa optrfr optvol palr panlr patr pcl pclr pcnlr pctr pdvc pi pidom pifo pll pltbl pnca pncad pncaeps pncia pncid pncieps pncip pncwia pncwid pncwieps pncwip pnlbl pnli pnrsho pobl ppcbl ppegt ppenb ppenc ppenli ppenls ppenme ppennr ppeno ppent ppevbb ppeveb ppevo ppevr pppabl ppphbl pppobl ppptbl prc prca prcad prcaeps prebl pri prodv prsho prstkcc prstkpc prvt pstkc pstkn ptbl ptran pvcl pvo pvon pvpl pvt pwoi radp ragr rari rati rca rcd rceps rcl rcp rdip rdipa rdipd rdipeps rdp re rea reajo recch recco recd recta rectr recub ret reuna reunr ris rll rlo rlp rlri rlt rmum rpag rra rrd rreps rrp rstche rstchelt rvbci rvbpi rvbti rvdo rvdt rveqt rvlrv rvno rvnt rvri rvsi rvti rvtxr rvupi rvutx saa sal salepfc salepfp sbdc sc sco scstkc secu seqo seta setd seteps setp siv spce spced spceeps spi spid spieps spioa spiop sppe sppiv spstkc sret srt ssnp stbo stio stkco stkcpa tdc tdscd tdsce tdsg tdslg tdsmm tdsng tdso tdss tdst teq tf tfva tfvce tfvl tie tii tlcf transa tsa tsafc tso tstk tstkc tstkme tstkn tstkp txach txbco txbcof txc txdb txdba txdbca txdbcl txdc txdfed txdfo txdi txds txeqa txeqii txfed txfo txndb txndba txndbl txndbr txo txpd txr txs txt txtubadjust txtubbegin txtubend txtubmax txtubmin txtubposdec txtubposinc txtubpospdec txtubpospinc txtubsettle txtubsoflimit txtubtxtr txtubxintbs txtubxintis txva txw uaoloch uaox uapt ucaps uccons uceq ucustad udcopres udd udfcc udmb udolt udpco udpfa udvp ufretsd ugi ui uinvt ulcm ulco uniami unl unnp unnpl unopinc unwcc uois uopi uopres updvp upmcstk upmpf upmpfs upmsubp upstk upstkc upstksf urect urectr urevub uspi ustdnc usubdvp usubpstk utfdoc utfosc utme utxfed uwkcapc uxinst uxintd vpac vpo wcap wcapc wcapch wda wdd wdeps wdp xago xagt xcom xcomi xdepl xdp xdvre xeqo xi xido xidoc xindb xindc xins xinst xintd xintopt xivi xivre xlr xnbi xnf xnins xnitb xobd xoi xopr xoprar xoptd xopteps xore xpr xrdp xrent xs xstf xstfo xstfws xt xuw xuwli xuwnli xuwoi xuwrei xuwti fic cshtr_c dvpsp_c dvpsx_c prcc_c prch_c prcl_c adjex_c cshtr_f dvpsp_f dvpsx_f mkvalt prcc_f prch_f prcl_f adjex_f rank au auop auopic ceoso cfoso add1 add2 add3 add4 addzip city county dlrsn ein fax idbflag incorp loc phone prican prirow priusa state stko weburl ipodate dldte spcsrc costat



*gen fyear_end_year																
gen fyear_end_year=yofd(datadate), after(datadate)								
gen fyear_end_month=month(datadate), after(fyear_end_year)	
mdesc fyear_end_year fyear														
label var fyear_end_year "Fiscal year end based on datadate from COMPUSTAT"
label var fyear_end_month "Fiscal year end month based on datadate from COMPUSTAT"

tab fyear
tab fyear_end_year
drop if fyear < 1975												// Similar as in Belo et al (RED2014) and Vitorino (MS2014) since very little reporting of advertisign before that. 
drop if fyear > 2020												// end of our sample.	
tab fyear_end_year
tab fyear			
order fyr, after(fyear)


tab curcd, sort			
keep if curcd == "USD"															// keep those with native currency of US dollar 


drop LINKPRIM LINKTYPE LINKDT LINKENDDT indfmt datafmt consol curcd popsrc

mdesc sic sich naics naicsh														

rename *, lower																	//rename all vars that are all CAPS
rename lpermno permno															//change name to fit CRSP file names --> is merger var with CRSP
rename lpermco permco															//change name to fit CRSP file names

destring cik, replace															//now numeric var
mdesc gvkey																		


* clean up sic codes and create Fama-French Industry classifications																	
destring sic, replace 
destring sich, replace 															
mdesc sic sich																	

replace sich = sic if mi(sich)													//Use historical SIC as default, but replace it with the current one if it is missing 
mdesc sic sich																	

* Fama-French 49 industry classifications
ffind sich, newvar(ffind49) type(49)
mdesc ffind49  																	
label var ffind49 "FF industry code (49) using historical SIC from COMPUSTAT"

drop if mi(sale)	
drop if sale <= 0																
drop if mi(at)																	
drop if at <= 0																	

* keep unique observation (fyr=fiscal year end month)
bys permno fyear_end_year (fyr): gen n=_n
bys permno fyear_end_year (fyr): gen N=_N
browse gvkey permno permco datadate fyear_end_year fyear_end_month fyear fyr sic sich n N if N==2
bys permno fyear (fyr): keep if _n==_N									
drop n N


******************************************
*** Indicate that var come from COMPUSTAT.
******************************************
foreach var of varlist gvkey datadate fyear fyr conm conml apdedate fdate cik fyrc {
	local lbl : variable label `var'
    label var `var' `"`lbl' from COMPUSTAT"'
}



******************
*** Save the current dataset temporarily 
******************
tempfile CCM
save `CCM'								// save as a temp file temporarily; the file automatically gets deleted after closing this do-file.
****************************************

*********************************************************************************
** construct Brand capital and organization capital to be merged with CCM data.
*********************************************************************************
* check availability of advertising variable
*duplicates drop permno fyear, force
mdesc xad	xsga																	
count if xad == 0  															
count if xsga == 0

* drop important variables similar as all studies in organization capital and brand value.
drop if mi(xsga)  															 	
drop if xsga < 0
drop if xad < 0																	

drop if sich >= 6000 & sich <= 6999			/* Exclude financial firms  according to Belo et al (RED2014) and other studies in this field */

tempfile temp
save `temp'

import excel "cpi.xlsx", sheet("Sheet1") firstrow clear
rename year fyear

merge 1:m fyear using `temp'
keep if _merge == 3 
drop _merge

* Deflate relevant series to be used in perpetual inventory method
replace cpi = cpi/100
foreach var of varlist xad xsga at{
		gen `var'_real = `var'/cpi
		}

xtset permno fyear



/* Org Capital */

* Paramters to estimate organization capital

local delta = 0.15		/* Depreciation rate:   */
local g = 0.1			/* Average real growth rate of firm-level SG&A according to Eisfeldt JF 2013.  */
local theta = 1			/* Fraction of advertising to be used as investment in brands: 1 means that we take all SG&A as investment on intangibles as in Eisfelt. */

* Compute org capital following Eisfeldt and Papanikoalou (2013)

g orgcap = .
bys permno (fyear): replace orgcap = (1-`delta')*`theta'*xsga_real/(`g'+`delta') ///
	+ `theta'*xsga_real if _n==1
bys permno (fyear): replace orgcap = (1-`delta')*orgcap[_n-1] + `theta'*xsga_real ///
	if fyear==fyear[_n-1]+1 & _n>1

replace orgcap = . if orgcap < 0   							
replace orgcap = . if orgcap == 0 							

g orgcap_to_assets = orgcap/at_real
label var orgcap_to_assets "orgcap/total assets"



/* Brand Capital */

* Paramters to estimate brand capital

local delta = 0.2		/* Depreciation rate: Belo et al (RED2014) used 50% whereas Vitorino MS2014 and Belo et al (JFE2022) uses 20%.  */
local g = 0.1			/* Average real growth rate of firm-level advertising expenses. Both Belo RED2014 and Vitorino MS2014 uses 10%.  */
local theta = 1			/* Fraction of advertising to be used as investment in brands: 1 means that we take all advertising expenses as investments on brand capital. */


* Compute brand capital following Belo et al
g brandcap = .
bys permno (fyear): replace brandcap = (1-`delta')*`theta'*xad_real/(`g'+`delta') ///
	+ `theta'*xad_real if _n==1
bys permno (fyear): replace brandcap = (1-`delta')*brandcap[_n-1] + `theta'*xad_real ///
	if fyear==fyear[_n-1]+1 & _n>1
	
replace brandcap = . if brandcap < 0 
replace brandcap = . if brandcap == 0 

g brandcap_to_assets = brandcap/at_real
label var brandcap_to_assets "brandcap/total assets"

* construct scaled advertising and R&D variables
g xrd_to_sale = xrd/sale
replace xrd_to_sale = . if xrd <= 0
g xad_to_sale = xad/sale
replace xad_to_sale = . if xad_to_sale <= 0
g xsga_to_sale = xad/sale
replace xsga_to_sale = . if xsga_to_sale <= 0

mdesc orgcap_to_assets brandcap_to_assets xad_to_sale xrd_to_sale xsga_to_sale xad xrd xsga
summ orgcap_to_assets brandcap_to_assets, d 

*keep the variables needed
sort permno fyear
keep permno fyear orgcap brandcap orgcap_to_assets brandcap_to_assets xad_to_sale xrd_to_sale xsga_to_sale 


***************************************************************************
** Merge with the main compustat data and the brand value data from Interbrand
**************************************************************************
merge 1:1 permno fyear using `CCM'
drop _merge

merge 1:1 permno fyear using brandvalue						
drop _merge


* monthly data_Set
gen time_avail_m = mofd(datadate) + 6  // Assuming 6 month reporting lag
format time_avail_m %tm
order time_avail_m, after(datadate)

gen temp = 12
expand temp
drop temp
sort gvkey datadate

gen tempTime = time_avail_m
bysort gvkey tempTime: replace time_avail_m = time_avail_m + _n - 1 

drop tempTime
bysort gvkey time_avail_m (datadate): keep if _n == _N  
bysort permno time_avail_m (datadate): keep if _n == _N  
drop permco cusip liid tic pdate busdesc ggroup gind gsector gsubind spcindcd spcseccd exchg
destring gvkey, replace 


******************
*** Save tmp file (Compustat with brand cap, orgcap, brandvalue.
******************
cd "$output_file_path"
save CCM, replace		
************************



*******************************************************************************
*** 2. Prepare CRSP monthly file for merge, i.e. merge with COMPUSTAT tempfile.
*******************************************************************************

cd "$data_path"
use $crsp_file_name, clear

* keep only ordinary shares + main exchanges from Bali/Engle/Murray (2016) + Chen (2020)
rename *, lower																	//rename all vars that are all CAPS
distinct permno																	
keep if shrcd==10 | shrcd==11													
keep if exchcd==1 | exchcd==2 | exchcd==3										//1=NYSE; 2=AMEX; 3=NASDAQ --> 
											

*delete duplicate firm months																		
duplicates tag permno date, g(dup)				
tab dup																			
duplicates drop permno date, force												//
drop dup

* create month_id
gen time_avail_m = mofd(date)
format time_avail_m %tm
order time_avail_m, after(date)
*drop date
 

tsset permno time_avail_m

* keep only if valid namedt and nameendt from from Bali/Engle/Murray (2016) + Chen (2020)
gen byte bad_return=date>nameendt, after(date)
tab bad_return																	
drop bad_return nameendt

* turn bid-ask quotes into price + create market equity

replace altprc = abs(altprc)
					
gen ME = (altprc*shrout)/1000													//from Bali/Engle/Murray (2016); has way less missings than prc field approach 2,000 (0.13%) missings
mdesc prc altprc ME															
distinct permno															

tsset permno time_avail_m																	
gen ME_L1=l1.ME																	

label var ME "Market Equity in million $"
label var ME_L1 "Lagged Market Equity of previous month"
mdesc ME ME_L1

* Incorporate delisting return
mdesc ret																		
replace dlret =-.35 if dlret==. & (dlstcd == 500 | (dlstcd >=520 & dlstcd <=584)) & (exchcd == 1 | exchcd == 2)	
replace dlret =-.55 if dlret==. & (dlstcd == 500 | (dlstcd >=520 & dlstcd <=584)) & exchcd == 3  
replace dlret = -1 if dlret < -1 & dlret !=.									
replace ret = dlret if ret ==. & dlret !=0										
replace ret = dlret if ret ==. & dlret ==0										
mdesc ret																																		

*only keep years with min 10 valid returns
gen year=yofd(date)																//creates year from date var IMPORTANT year is fiscal year in COMPUSTAT
gen ret_missing=1 if ret==.
bys permno year: egen missing_return_per_year= total(ret_missing), missing
tab missing_return_per_year
gen byte tobedroped= missing_return_per_year>2 & missing_return_per_year!=.
tab tobedroped																	
mdesc ME if tobedroped==1																											
drop if tobedroped == 1
drop tobedroped missing_return_per_year ret_missing
*only keep stocks with ME in June (month=6) as this is needed for portfolio construction
gen month = month(date)															//creates month count from 1 to 12 from date var
gen ME_june_missing=1 if ME==. & month==6										
bys permno year: egen missing_ME_june_year= total(ME_june_missing), missing
tab missing_ME_june_year														
bys permno: egen max_year=max(year)						 
gen byte tobedroped= missing_ME_june_year==1 & year!=max_year
tab tobedroped																	
drop if tobedroped==1			

rename year fyear_end_year														


keep permno date time_avail_m month fyear_end_year siccd comnam naics hsiccd shrout ME shrcd exchcd prc vol ret vwretd vwretx ewretd ewretx
mdesc permno date time_avail_m month fyear_end_year siccd comnam naics hsiccd shrout ME shrcd exchcd prc vol ret vwretd vwretx ewretd ewretx
order fyear_end_year month, after(date)


******************************
*** 3. Merge with FF3 monthly.													
******************************
cd "$data_path"
rename time_avail_m t
merge m:1 t using factors
tab _merge	
keep if _merge==3
drop _merge	
rename t time_avail_m


*********************************
*** 4. Merge Compustat with CRSP.												
*********************************
cd "$output_file_path"
merge 1:1 permno time_avail_m using CCM										
tab _merge	
keep if _merge==3 | _merge == 1
drop _merge																	

order datadate fyear_end_month fyear fyr, after(time_avail_m)

xtset permno time_avail_m
*******************************
*** e.) Size - needs shrout prc 													//shrout = monthly Shares outstanding; prc = monthly closing Price both from CRSP	
*******************************
replace shrout = shrout/1000
gen mve_c = (shrout * abs(prc))
gen Size = log(mve_c)
label var Size "Log Size"

**********************************
*** f.) Book-to-market - needs ceq	 											//ceq = Common Equity - total from COMPUSTAT
**********************************
gen BM = log(ceq/mve_c)															
label var BM "Log Book-to-market"

****************************
*** g.) Momentum - needs ret 	 												//ret = montly Return from CRSP
****************************
replace ret = 0 if mi(ret)
gen Mom12m = ( (1+l.ret)*(1+l2.ret)*(1+l3.ret)*(1+l4.ret)*(1+l5.ret)*(1+l6.ret)*(1+l7.ret)*(1+l8.ret)*(1+l9.ret)*(1+l10.ret)*(1+l11.ret) ) - 1
label var Mom12m "Twelve month momentum"


**********************************
*** j.) Beta - needs ret rf ewretd												
**********************************
gen retrf = ret - rf
gen ewmktrf = vwretd - rf

xtset permno time_avail_m

asreg retrf ewmktrf, window(time_avail_m 60) min(20) by(permno)
rename _b_ewmktrf Beta
label var Beta "CAPM Beta"
drop _* retrf ewmktrf


**********************************
*** l.) DolVol - needs vol prc vol												//vol = amount of shares traded from CRSP; prc = monthly closing price from CRSP
**********************************
replace vol =  vol/10^4
xtset permno time_avail_m
gen DolVol = log(l2.vol*abs(l2.prc))
label var DolVol "Past trading volume"


*******************************
*** o.) Asset Growth - needs at													//at = Assets
*******************************
xtset permno time_avail_m
gen AssetGrowth = (at - l12.at)/l12.at 
label var AssetGrowth "Asset Growth"

************************************************
*** p.) OpenProf - needs revt cogs xsga xint ceq									//revt = Revenue - Total; cogs = Cost of Goods Sold; xsga = Selling, General and Administrative Expense; xint = Interest and Related Expense - Total; ceq = Common/Ordinary Equity - Total
************************************************
gen byte missing_cogs=cogs==.
gen byte missing_xsga=xsga==.
gen byte missing_xint=xint==.

gen cogs_h=cogs
replace cogs_h=0 if cogs_h==.

gen xsga_h=xsga
replace xsga_h=0 if xsga_h==.

gen xint_h=xint 
replace xint_h=0 if xint_h==.

gen tempprof = (revt - cogs_h - xsga_h - xint_h)/ceq if ceq > 0 & (missing_cogs==0 | missing_xsga==0 | missing_xint==0)

gen OperProf = tempprof
label var OperProf "Operating Profitability"
drop tempprof 

drop cogs_h xsga_h xint_h



*******************************
*** s.) AdExp - needs xad mve_c													//xad = Advertising Expense; mve_c = market cap
*******************************
gen AdExp_to_ME 	= xad/mve_c
replace AdExp_to_ME = . if xad <= 0 
label var AdExp_to_ME "Advertising Expenses-to-market cap"

label var xad_to_sale "Advertising Expenses-to-market cap"
rename xad_to_sale AdExp_to_Sale
****************************
*** t.) RD - needs xrd mve_c														//xrd = Research and Development Expense; mve_c = market cap
****************************
gen RD_to_ME = xrd/mve_c
label var RD_to_ME "R&D-to-market cap"

label var xrd_to_sale "R&D-to-Sale"
rename xrd_to_sale RD_to_Sale

replace RD_ME = 0 if RD_ME == .
replace RD_Sale = 0 if RD_Sale == .

*************************************************************************************
*** v.) CBOperProf - needs revt cogs xsga xrd rect invt xpp drc drlt ap xacc BM mve_c  //revt = Revenue - Total; cogs = Cost of Goods Sold; xsga = Selling, General and Administrative Expense; xrd = Research and Development Expense; rect = Receivables - Total; invt = Inventories - Total; xpp = Prepaid Expenses; drc = Deferred Revenue - Current; drlt = Deferred Revenue - Long-term; ap = Accounts Payable - Trade; xacc = Accrued Expenses
*************************************************************************************
foreach v of varlist revt cogs xsga xrd rect invt xpp drc drlt ap xacc {
	replace `v' = 0 if mi(`v')
}
gen CBOperProf = (revt - cogs - (xsga - xrd)) - (rect - l12.rect) - (invt - l12.invt) - (xpp - l12.xpp) + (drc + drlt - l12.drc - l12.drlt) + (ap - l12.ap) + (xacc - l12.xacc)
replace CBOperProf = CBOperProf/at

replace CBOperProf = . if shrcd > 11 | mi(mve_c) | mi(BM) | mi(at) | (sich >= 6000 & sich < 7000)
label var CBOperProf "Cash-based Operating Profitability"


mdesc permno date fyear_end_year month time_avail_m datadate fyear_end_month fyear ret ME rf mve_c Size BM Mom12m  Beta  DolVol  AssetGrowth OperProf AdExp_to_ME AdExp_to_Sale RD_to_ME RD_to_Sale CBOperProf


cd "$output_file_path"
save $output_data_file_name, replace

*************************************************************************************
** variables using CRSP daily data. 
*******************************************************************************************************************************************************************************


******************
*** a.) ReturnSkew
******************
cd "$raw_data_path"
use $crsp_daily_file_name, clear

rename *, lower		
keep if shrcd==10 | shrcd==11													
keep if exchcd==1 | exchcd==2 | exchcd==3

gen time_avail_m = mofd(date)
format time_avail_m %tm
gen days = 0 
gcollapse (count) ndays = days (skewness) ReturnSkew = ret, by(permno time_avail_m)
replace ReturnSkew = . if ndays < 15
label var ReturnSkew "Return Skewness"
drop ndays
drop if time_avail_m>743														//12/2021

cd "$output_file_path"
compress
save ReturnSkew, replace 

*******************
*** b.) Illiquidity
*******************
cd "$raw_data_path"
use $crsp_daily_file_name, clear

rename *, lower		
keep if shrcd==10 | shrcd==11													
keep if exchcd==1 | exchcd==2 | exchcd==3

gen time_avail_m = mofd(date)
format time_avail_m %tm
gen double ill = abs(ret)/(abs(prc)*vol/1000000)
gcollapse (mean) ill, by(permno time_avail_m)
xtset permno time_avail_m
gen Illiquidity = (ill + l.ill + l2.ill + l3.ill + l4.ill + l5.ill + l6.ill + l7.ill + l8.ill + l9.ill + l10.ill + l11.ill)/12
label var Illiquidity "Illiquidity"
drop ill
drop if time_avail_m>743														//12/2021

cd "$output_file_path"
compress
save Illiquidity, replace 

******************************
*** c.) IdioRisk + IdioVolCAPM
******************************
cd "$raw_data_path"
use $crsp_daily_file_name, clear

rename *, lower		
keep if shrcd==10 | shrcd==11													
keep if exchcd==1 | exchcd==2 | exchcd==3
rename date day_id
merge m:1 day_id using united_states_factor_model_daily, keepusing(rf mktrf)
keep if _merge==3
drop _merge

replace ret = ret - rf
drop rf 
rename day_id time_d
* Set up CAPM to estimate idiovol
bys permno (time_d): gen time_temp = _n
xtset permno time_temp
* CAPM 
asreg ret mktrf, window(time_temp 20) min(15) by(permno) rmse
rename _rmse IdioRisk
gen epsReturnCAPM = ret - _b_cons - _b_mktrf*mktrf  // This is idiosyncratic return, skew computed below
drop if mi(IdioRisk)
gen time_avail_m = mofd(time_d)
format time_avail_m %tm
sort permno time_avail_m time_d
gcollapse (lastnm) IdioRisk (sd) IdioVolCAPM = epsReturnCAPM, by(permno time_avail_m)
label var IdioRisk "Idiosyncratic Risk"
label var IdioVolCAPM "Idiosyncratic Risk (CAPM)"
drop if time_avail_m>743														//12/2021

cd "$output_file_path"
compress
save IdioVolCAPM, replace 

*******************************
*** d. IdioVol3F + ReturnSkew3F
*******************************
cd "$raw_data_path"
use $crsp_daily_file_name, clear

rename *, lower		
keep if shrcd==10 | shrcd==11													
keep if exchcd==1 | exchcd==2 | exchcd==3
rename date day_id
merge m:1 day_id using united_states_factor_model_daily, keepusing(rf mktrf smb hml)
keep if _merge==3
drop _merge

replace ret = ret - rf
drop rf 
rename day_id time_d
// SIGNAL CONSTRUCTION
bys permno (time_d): gen time_temp = _n
xtset permno time_temp
* 3F model 
asreg ret mktrf smb hml, window(time_temp 20) min(15) by(permno)
gen epsReturn3F = ret - _b_cons - _b_mktrf*mktrf - _b_smb*smb - _b_hml*hml 
gen time_avail_m = mofd(time_d)
format time_avail_m %tm
sort permno time_avail_m time_d
gcollapse (sd) IdioVol3F = epsReturn3F (skewness) ReturnSkew3F = epsReturn3F, by(permno time_avail_m)
label var IdioVol3F "Idiosyncratic Risk (3 factor)"
label var ReturnSkew3F "Skewness of daily idiosyncratic returns (3F model)"
drop if time_avail_m>743														//12/2021

cd "$output_file_path"
compress
save IdioVol3F, replace 

********************************************************************************************************************************************************
* merge these datasets into the main data_Set
******************************************************
cd "$output_file_path"

merge 1:1 permno time_avail_m using $output_data_file_name
keep if _merge==3 | _merge==1
drop _merge
merge 1:1 permno time_avail_m using IdioVolCAPM
keep if _merge==3 | _merge==1
drop _merge
merge 1:1 permno time_avail_m using Illiquidity
keep if _merge==3 | _merge==1
drop _merge
merge 1:1 permno time_avail_m using ReturnSkew
keep if _merge==3 | _merge==1
drop _merge



**********************************************
*** Winsorize all controls at 1% on both ends. 
**********************************************
foreach var in Size BM Mom12m  Beta  DolVol  AssetGrowth OperProf  AdExp_to_ME AdExp_to_Sale RD_to_ME RD_to_Sale CBOperProf IdioVol3F ReturnSkew3F IdioRisk IdioVolCAPM Illiquidity ReturnSkew {
winsor `var', gen(`var'_w) p(0.01)	
order `var'_w, after(`var')
}


********************************************
** Merge with BB dummy
********************************************
cd "$data_path"
merge 1:1 permno time_avail_m using dummy_bb_v

drop if _merge == 2
drop _merge

********************************************
** Merge with industry returns
********************************************

ffind sich, newvar(IndNum) type(49)

merge m:1 IndNum time_avail_m using ret_ind				
drop if _merge == 2
drop _merge

replace ret_ind  = 0 if ret_ind == -99.99			

replace BB = 0 if BB == .


g Adv_ME_inc_missing = Adv_ME
replace Adv_ME_inc_missing = 0 if Adv_ME_inc_missing == .

g Adv_Sale_inc_missing = Adv_Sale
replace Adv_Sale_inc_missing = 0 if Adv_Sale_inc_missing == .

g BRANDCAP_inc_missing = BRANDCAP
replace BRANDCAP_inc_missing = 0 if BRANDCAP == .

g ORGCAP_inc_missing = ORGCAP
replace ORGCAP_inc_missing = 0 if ORGCAP == .

foreach var in Adv_ME Adv_Sale BRANDCAP  ORGCAP {
g `var'_missing_dummy = 0
replace `var'_missing_dummy = 1 if `var' == .
}

** merge with Best Companies to Work for data
rename fyear year
merge m:1 permno year using bc_list_2022
drop if _merge == 2
drop _merge
replace bc = 0 if bc == .
rename bc BC

gen excess_ret= ret-rf

gen excess_ret_ind= ret-ret_ind/100
xtset permno time_avail_m
gen f1_excess_ret=f1.excess_ret
gen f1_excess_ret_ind=f1.excess_ret_ind
*drop if time_avail_m<=488    // 200009
*drop if time_avail_m > 731  // 202012
xtset permno time_avail_m
	
g log_brandvalue = log(brandvalue) if brandvalue > 0 
replace log_brandvalue = 0 if log_brandvalue == .

g brand_importance = brandvalue/mve_c
summ brand_importance, d


rename Size_w LOGSIZE
rename Beta_w BETA
rename BM_w LOGBM
rename Mom12m_w MOM
rename OperProf_w OP
rename AssetGrowth_w INV
rename DolVol_w VOL
rename AdExp_to_ME_w Adv_ME
rename AdExp_to_Sale_w Adv_Sale 
rename RD_to_Sale_w RD_Sale
rename RD_to_ME_w RD_ME
rename log_brandvalue LogBrandValue
rename brandcap_to_assets BRANDCAP
rename orgcap_to_assets ORGCAP
rename IdioVol3F_w IDIOVOL3f
rename ReturnSkew3F_w SKEWNESS3F
rename IdioRisk_w IDIORISK
rename IdioVolCAPM_w IDIOVOL 
rename Illiquidity_w ILLIQ
rename ReturnSkew_w SKEWMESS



foreach i in f1_excess_ret f1_excess_ret_ind IDIOVOL {
       replace `i'=`i'*100
}

mdesc BB Size Beta BM Mom12m OperProf AssetGrowth
mdesc LOGSIZE  LOGBM MOM VOL  


keep if tin(2000m9, 2020m12)



**************************************************
*** 7. Save final data set for for Fama-MAcBeth regressions 
**************************************************
cd "$output_file_path"
save $output_data_file_name, replace


*******************************
*** 8. Fama/McBeth regressions. 
*******************************

cd "$output_file_path"
use $output_data_file_name, clear



*****************************
*** Table 6
*****************************

local MainVars0 = "BB BETA LOGSIZE LOGBM MOM"
local MainVars1 = "INV OP ILLIQ RD_Sale IDIOVOL SKEWMESS"
local depvar = "f1_excess_ret"

// f1_excess_ret_ind

asreg `depvar' `MainVars0',  fmb newey(1) 
outreg2 using Table6, replace stats(coef tstat) dec(2) sortvar(Constant) drop(`depvar') label excel


asreg `depvar' `MainVars0' `MainVars1',  fmb newey(1) 
outreg2 using Table6, append stats(coef tstat) dec(2) sortvar(Constant) drop(`depvar') label excel


asreg `depvar' `MainVars0' `MainVars1' Adv_ME ,  fmb newey(1) 
outreg2 using Table6, append stats(coef tstat) dec(2) sortvar(Constant) drop(`depvar') label excel


asreg `depvar' `MainVars0' `MainVars1' Adv_ME  BRANDCAP ,  fmb newey(1) 
outreg2 using Table6, append stats(coef tstat) dec(2) sortvar(Constant) drop(`depvar') label excel

asreg `depvar' `MainVars0' `MainVars1' Adv_ME  BRANDCAP ORGCAP,  fmb newey(1) 
outreg2 using Table6, append stats(coef tstat) dec(2) sortvar(Constant) drop(`depvar') label excel



***************************
** Table 8 Panel B
***************************
local MainVars0 = "BB BETA LOGSIZE LOGBM MOM"
local MainVars0_BC = "BB BC BETA LOGSIZE LOGBM MOM"
local depvar = "f1_excess_ret"
local MainVars1 = "INV OP ILLIQ RD_Sale IDIOVOL SKEWMESS"
local output_name = "Table8_PanelB"


asreg `depvar' `MainVars0',  fmb newey(1) 
outreg2 using `output_name', replace stats(coef tstat) dec(2) sortvar(Constant) drop(`depvar') label excel

asreg `depvar' `MainVars0_BC',  fmb newey(1) 
outreg2 using `output_name', append stats(coef tstat) dec(2) sortvar(Constant) drop(`depvar') label excel

asreg `depvar' `MainVars0' `MainVars1',  fmb newey(1) 
outreg2 using `output_name', append stats(coef tstat) dec(2) sortvar(Constant) drop(`depvar') label excel

asreg `depvar' `MainVars0_BC' `MainVars1',  fmb newey(1) 
outreg2 using `output_name', append stats(coef tstat) dec(2) sortvar(Constant) drop(`depvar') label excel




********************
** Table 9 
*******************

replace intan = 0 if intan == .
g intangibles_over_assets = intan/at
summ intangibles_over_assets, d
bys year BB: egen median_int = median(intangibles_over_assets)

g BB_H_intan = 0
replace BB_H_intan = 1 if BB == 1 & intangibles_over_assets > median_int

g BB_L_intan = 0
replace BB_L_intan = 1 if BB == 1 & intangibles_over_assets <= median_int

****
local MainVars0 = "BB_H_intan BB_L_intan BETA LOGSIZE LOGBM MOM"
local MainVars1 = "INV OP ILLIQ RD_Sale IDIOVOL SKEWMESS"
local output_name = "Table9"
local depvar = "f1_excess_ret"
keep if tin(2000m9, 2020m12)


asreg `depvar' `MainVars0' `MainVars1',  fmb newey(1) 
outreg2 using `output_name', replace stats(coef tstat) dec(2) sortvar(Constant) drop(`depvar') label excel

asreg `depvar' `MainVars0' `MainVars1' Adv_ME ,  fmb newey(1) 
outreg2 using `output_name', append stats(coef tstat) dec(2) sortvar(Constant) drop(`depvar') label excel

asreg `depvar' `MainVars0' `MainVars1' Adv_ME_inc_missing ,  fmb newey(1) 
outreg2 using `output_name', append stats(coef tstat) dec(2) sortvar(Constant) drop(`depvar') label excel

asreg `depvar' `MainVars0' `MainVars1' Adv_ME_inc_missing BRANDCAP_inc_missing ORGCAP_inc_missing,  fmb newey(1) 
outreg2 using `output_name', append stats(coef tstat) dec(2) sortvar(Constant) drop(`depvar') label excel

local depvar = "f1_excess_ret_ind"
asreg `depvar' `MainVars0' `MainVars1',  fmb newey(1) 
outreg2 using `output_name', append stats(coef tstat) dec(2) sortvar(Constant) drop(`depvar') label excel

asreg `depvar' `MainVars0' `MainVars1' Adv_ME ,  fmb newey(1) 
outreg2 using `output_name', append stats(coef tstat) dec(2) sortvar(Constant) drop(`depvar') label excel

asreg `depvar' `MainVars0' `MainVars1' Adv_ME_inc_missing ,  fmb newey(1) 
outreg2 using `output_name', append stats(coef tstat) dec(2) sortvar(Constant) drop(`depvar') label excel

asreg `depvar' `MainVars0' `MainVars1' Adv_ME_inc_missing BRANDCAP_inc_missing ORGCAP_inc_missing,  fmb newey(1) 
outreg2 using `output_name', append stats(coef tstat) dec(2) sortvar(Constant) drop(`depvar') label excel


